drop proc spa_SEC_GetAccessFunctions
go
/*********************************************************************************************
* PROCEDURE:	[spa_SEC_GetAccessFunctions]
* PURPOSE: Get access functions list for Manage User Form.
* NOTES:
* CREATED:	Minh Nguyen	3/31/2011
* MODIFIED:	

'-------------------------------------------------------------------------------------------------*
' Date       | Version | Ticket #    |       Author             | Description
' ========== | ======= | =========== | =========================| ====================================================================
' 3/31/2011  | 1.0      | 16131      | Minh Nguyen             | CREATED NEW.
'-------------------------------------------------------------------------------------------------*
exec spa_SEC_GetAccessFunctions
	@UserRecID = '20B84CBE-D47E-45D7-9230-0CE63FCDE8BB',
	@EditUserRecID = '173EAF56-64BF-4F06-A4ED-8DD920BAD2A7',
	@UserRole = 2,
	@SelectedRoleId = 4,
	@Group = 'AccessFunctions',
	@Type = 'func'
*/

CREATE PROCEDURE [spa_SEC_GetAccessFunctions]
	@UserRecID uniqueidentifier,
	@EditUserRecID uniqueidentifier = null,
	@UserRole int,
	@SelectedRoleId int,
	@Group varchar(50)=null,
	@Type varchar(50)=null
AS
CREATE TABLE #FinalFunctions
(
	PermissionId int,
	[Description] nvarchar(256),
	PermissionCode nvarchar(256),
	[Group] varchar(50),
	[Type] varchar(50),
	DisplayOrder int,
	IsEnabled bit, -- the logged in user can check on this function	
	IsChecked bit -- the edited user currently has this function
)
if @Group = '' set @Group = null
if @Type = '' set @Type = null
INSERT INTO #FinalFunctions(PermissionId, [Description], PermissionCode, [Group], [Type], DisplayOrder, IsEnabled, IsChecked)
SELECT PermissionId, b.Description, PermissionCode, b.[Group] , b.[Type] , b.DisplayOrder, 1, 0
FROM SEC_Permissions b (nolock)
WHERE (b.[Group] = @Group or @Group is null) and (b.[Type] = @Type or @Type is null)
--AND PermissionId in (SELECT PermissionId FROM SEC_PermissionsInRole (nolock) WHERE [RoleId] = @SelectedRoleId)
order by b.DisplayOrder ASC

UPDATE #FinalFunctions SET IsEnabled = 0
WHERE PermissionId NOT IN (SELECT PermissionId FROM SEC_PermissionsInRole (nolock) WHERE [RoleId] = @UserRole) 
	OR PermissionId IN (SELECT PermissionId FROM SEC_UserPermissionExcluded (nolock) WHERE UserRecId = @UserRecID)
	OR PermissionId NOT IN (SELECT PermissionId FROM SEC_PermissionsInRole (nolock) WHERE [RoleId] = @SelectedRoleId) 

IF @EditUserRecID IS NOT NULL
BEGIN
	UPDATE #FinalFunctions SET IsChecked = 1
	WHERE PermissionId IN (SELECT PermissionId FROM SEC_PermissionsInRole (nolock) WHERE [RoleId] = @SelectedRoleId) 
		AND PermissionId NOT IN (SELECT PermissionId FROM SEC_UserPermissionExcluded (nolock) WHERE UserRecId = @EditUserRecID)
	
END

SELECT * FROM #FinalFunctions
GO